﻿using System;
using System.Collections.Generic;
using System.Data.SqlClient;
using System.Data;
using System.Xml;
using System.Runtime.Serialization;
using frag_boardgame;
using System.IO;

namespace DataBase
{
    public class DBAdapter : IDBAdapter
    {

        #region IDBAdapter Members

        public bool isUserNameAvailable(string username)
        {
            SqlDataAdapter sqlda = new SqlDataAdapter("SELECT ID, username, password, email, stats FROM dbo.accounts where username = '" + username + "'", "Data Source=frag-boardgame.kicks-ass.org\\SQLEXPRESS;Initial Catalog=frag;Persist Security Info=True;User ID=roger;Password=rabbit");

            DataSet ds = new DataSet();

            DataTable dt = new DataTable();
            dt.Columns.Add("ID");
            dt.Columns.Add("username");
            dt.Columns.Add("password");
            dt.Columns.Add("email");
            dt.Columns.Add("stats");

            ds.Tables.Add(dt);

            sqlda.Fill(dt);
            

            if (dt.Rows.Count > 0) return false;
            else return true;
        }

        public DBPlayerInfo checkUserPassword(string username, string password)
        {
            DBPlayerInfo playerInfo;

            SqlDataAdapter sqlda = new SqlDataAdapter("SELECT ID, username, password, email, stats, gamesPlayed, gamesWon, Score FROM dbo.accounts where username = '" + username + "' AND password = '" + password + "'", "Data Source=frag-boardgame.kicks-ass.org\\SQLEXPRESS;Initial Catalog=frag;Persist Security Info=True;User ID=roger;Password=rabbit");

            DataSet ds = new DataSet();

            DataTable dt = new DataTable();
            dt.Columns.Add("ID");
            dt.Columns.Add("username");
            dt.Columns.Add("password");
            dt.Columns.Add("email");
            dt.Columns.Add("stats");
            dt.Columns.Add("gamesPlayed");
            dt.Columns.Add("gamesWon");
            dt.Columns.Add("score");

            ds.Tables.Add(dt);

            sqlda.Fill(dt);

            if (dt.Rows.Count > 0)
            {
                playerInfo = new DBPlayerInfo(Int32.Parse((String)dt.Rows[0].ItemArray[0]), (String)dt.Rows[0].ItemArray[1], (String)dt.Rows[0].ItemArray[2], (String)dt.Rows[0].ItemArray[3], float.Parse((String)dt.Rows[0].ItemArray[4]), Int32.Parse((String)dt.Rows[0].ItemArray[5]), Int32.Parse((String)dt.Rows[0].ItemArray[6]), Int32.Parse((String)dt.Rows[0].ItemArray[7]));
            }
            else playerInfo = null;
            return playerInfo;
        }

        public bool addAccount(string username, string password, string email)
        {
           

            if (!isUserNameAvailable(username))
                return false;
                   
           
            SqlDataAdapter sqlda = new SqlDataAdapter("SELECT ID, username, password, email, stats FROM dbo.accounts", "Data Source=frag-boardgame.kicks-ass.org\\SQLEXPRESS;Initial Catalog=frag;Persist Security Info=True;User ID=roger;Password=rabbit");

            DataSet ds = new DataSet();

            DataTable dt = new DataTable();
            dt.Columns.Add("ID");
            dt.Columns.Add("username");
            dt.Columns.Add("password");
            dt.Columns.Add("email");
            dt.Columns.Add("stats");

            ds.Tables.Add(dt);

            sqlda.Fill(dt);

            object[] newLine = new object[5];
            newLine[1] = username;
            newLine[2] = password;
            newLine[3] = email;
            newLine[4] = 0.5f;
            dt.Rows.Add(newLine);

            DataTable updates = dt.GetChanges();

            sqlda.InsertCommand = new SqlCommand("INSERT INTO dbo.accounts(username, password, email, stats) VALUES (\'" + username + "\',\'" + password + "\',\'" + email + "\',0.5)");


            sqlda.InsertCommand.Connection = new SqlConnection("Data Source=frag-boardgame.kicks-ass.org\\SQLEXPRESS;Initial Catalog=frag;Persist Security Info=True;User ID=roger;Password=rabbit");

            sqlda.Update(updates);

           
            return true;
        }





        public bool deleteUser(DBPlayerInfo playerInfo)
        {
            return deleteUser(playerInfo.ID);
        }

        public bool deleteUser(int ID)
        {

            SqlDataAdapter sqlda = new SqlDataAdapter("SELECT ID, username, password, email, stats FROM dbo.accounts WHERE ID=" + ID.ToString(), "Data Source=frag-boardgame.kicks-ass.org\\SQLEXPRESS;Initial Catalog=frag;Persist Security Info=True;User ID=roger;Password=rabbit");

            DataSet ds = new DataSet();

            DataTable dt = new DataTable();
            dt.Columns.Add("ID");
            dt.Columns.Add("username");
            dt.Columns.Add("password");
            dt.Columns.Add("email");
            dt.Columns.Add("stats");

            ds.Tables.Add(dt);

            sqlda.Fill(dt);

            if (dt.Rows.Count == 1)
            {
                dt.Rows[0].Delete();
            }

            else return false;

            DataTable updates = dt.GetChanges(DataRowState.Deleted);

            sqlda.DeleteCommand = new SqlCommand("DELETE from dbo.accounts WHERE ID=" + ID.ToString());

            sqlda.DeleteCommand.Connection = new SqlConnection("Data Source=frag-boardgame.kicks-ass.org\\SQLEXPRESS;Initial Catalog=frag;Persist Security Info=True;User ID=roger;Password=rabbit");

            sqlda.Update(updates);

            return true;
        }

        public DBPlayerInfo checkUsername(string username)
        {
            DBPlayerInfo playerInfo;

            SqlDataAdapter sqlda = new SqlDataAdapter("SELECT ID, username, password, email, stats, gamesPlayed, gamesWon, Score FROM dbo.accounts where username = '" + username + "'", "Data Source=frag-boardgame.kicks-ass.org\\SQLEXPRESS;Initial Catalog=frag;Persist Security Info=True;User ID=roger;Password=rabbit");

            DataSet ds = new DataSet();

            DataTable dt = new DataTable();
            dt.Columns.Add("ID");
            dt.Columns.Add("username");
            dt.Columns.Add("password");
            dt.Columns.Add("email");
            dt.Columns.Add("stats");
            dt.Columns.Add("gamesPlayed");
            dt.Columns.Add("gamesWon");
            dt.Columns.Add("score");

            ds.Tables.Add(dt);

            sqlda.Fill(dt);

            if (dt.Rows.Count > 0)
            {
                playerInfo = new DBPlayerInfo(Int32.Parse((String)dt.Rows[0].ItemArray[0]), (String)dt.Rows[0].ItemArray[1], (String)dt.Rows[0].ItemArray[2], (String)dt.Rows[0].ItemArray[3], float.Parse((String)dt.Rows[0].ItemArray[4]), Int32.Parse((String)dt.Rows[0].ItemArray[5]), Int32.Parse((String)dt.Rows[0].ItemArray[6]), Int32.Parse((String)dt.Rows[0].ItemArray[7]));
            }
            else playerInfo = null;
            return playerInfo;
        }

        public bool updatePlayerInfo(DBPlayerInfo playerInfo)
        {
            SqlDataAdapter sqlda = new SqlDataAdapter("SELECT ID, username, password, email, stats, gamesPlayed, gamesWon, Score FROM dbo.accounts WHERE ID=" + playerInfo.ID.ToString(), "Data Source=frag-boardgame.kicks-ass.org\\SQLEXPRESS;Initial Catalog=frag;Persist Security Info=True;User ID=roger;Password=rabbit");

            DataSet ds = new DataSet();

            DataTable dt = new DataTable();
            dt.Columns.Add("ID");
            dt.Columns.Add("username");
            dt.Columns.Add("password");
            dt.Columns.Add("email");
            dt.Columns.Add("stats");
            dt.Columns.Add("gamesPlayed");
            dt.Columns.Add("gamesWon");
            dt.Columns.Add("score");

            ds.Tables.Add(dt);

            sqlda.Fill(dt);

            if (dt.Rows.Count == 1)
            {
                dt.Rows[0].ItemArray[0] = playerInfo.ID.ToString();
                dt.Rows[0].ItemArray[1] = playerInfo.Username;
                dt.Rows[0].ItemArray[2] = playerInfo.Password;
                dt.Rows[0].ItemArray[3] = playerInfo.Email;
                dt.Rows[0].ItemArray[4] = playerInfo.Stats;
                dt.Rows[0].ItemArray[5] = playerInfo.GamesPlayed;
                dt.Rows[0].ItemArray[6] = playerInfo.GamesWon;
                dt.Rows[0].ItemArray[7] = playerInfo.Score;
                dt.Rows[0].SetModified();
            }

            else return false;

            DataTable updates = dt.GetChanges(DataRowState.Modified);

            sqlda.UpdateCommand = new SqlCommand("UPDATE dbo.accounts SET gamesPlayed=" + playerInfo.GamesPlayed.ToString() + ", gamesWon=" + playerInfo.GamesWon.ToString() + ", Score=" + playerInfo.Score.ToString() + "WHERE ID='" + playerInfo.ID.ToString() + "'");

            sqlda.UpdateCommand.Connection = new SqlConnection("Data Source=frag-boardgame.kicks-ass.org\\SQLEXPRESS;Initial Catalog=frag;Persist Security Info=True;User ID=roger;Password=rabbit");

            sqlda.Update(updates);

            return true;
        }

        #endregion

        #region IDBAdapter Members


        public bool addScoreToPlayer(string username, int score)
        {
            SqlCommand cmd = new SqlCommand();
            Int32 rowsAffected=0;

            try
            {

                cmd.CommandText = "dbo.frag_addScore_byName";
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Connection = new SqlConnection("Data Source=frag-boardgame.kicks-ass.org\\SQLEXPRESS;Initial Catalog=frag;Persist Security Info=True;User ID=roger;Password=rabbit");

                cmd.Parameters.Add(new SqlParameter("@i_user_name",username));
                cmd.Parameters.Add(new SqlParameter("@i_score", score));

                cmd.Connection.Open();
                rowsAffected = cmd.ExecuteNonQuery();
                cmd.Connection.Close();
            }
            catch (Exception e)
            {
                
            }

            if (rowsAffected == 0) return false;
            else return true;
        }


        public int getPlayerScore(string username)
        {

            SqlCommand cmd = new SqlCommand();
            int score = 0;
            try
            {

                cmd.CommandText = "dbo.frag_getUserScore_byName";
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Connection = new SqlConnection("Data Source=frag-boardgame.kicks-ass.org\\SQLEXPRESS;Initial Catalog=frag;Persist Security Info=True;User ID=roger;Password=rabbit");

                cmd.Parameters.Add(new SqlParameter("@i_user_name", username));

                cmd.Connection.Open();
                score = (int)cmd.ExecuteScalar();

                cmd.Connection.Close();

            }
            catch (Exception e)
            {
                score = -1;
            }
            return score;
        }


        public int createGameEnty(string gameName, DateTime time)
        {
            SqlCommand cmd = new SqlCommand();
            int gameID = -1;
            try
            {
                cmd.CommandText = "dbo.frag_createGameEntry";
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Connection = new SqlConnection("Data Source=frag-boardgame.kicks-ass.org\\SQLEXPRESS;Initial Catalog=frag;Persist Security Info=True;User ID=roger;Password=rabbit");

                cmd.Parameters.Add(new SqlParameter("@i_name", gameName));
               
                cmd.Connection.Open();
                gameID = (int)cmd.ExecuteScalar();

                cmd.Connection.Close();
            }
            catch (Exception e)
            {
                gameID = -1;
            }
            return (int)gameID;
        }


        public bool createHistoryEntry(int gameID, string userName)
        {
            SqlCommand cmd = new SqlCommand();
            bool success = true;
            
            try
            {
                cmd.CommandText = "dbo.frag_createHistoryEntry";
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Connection = new SqlConnection("Data Source=frag-boardgame.kicks-ass.org\\SQLEXPRESS;Initial Catalog=frag;Persist Security Info=True;User ID=roger;Password=rabbit");

                cmd.Parameters.Add(new SqlParameter("@i_user_name", userName));
                cmd.Parameters.Add(new SqlParameter("@i_game_id", gameID));

                cmd.Connection.Open();
                int rowsAffected = (int)cmd.ExecuteNonQuery();

                cmd.Connection.Close();
                if (rowsAffected == 0) success = false;
            }
            catch (Exception e)
            {
                success = false;
            }
            return success;
        }

        public List<String> getPlayerHistory(string username)
        {
            // currently it returns list of strings created at the SQL server.
            // the DB supports retreiving raw data.
            SqlDataReader sqldr;
            List<String> historyList = new List<string>();

            SqlCommand cmd = new SqlCommand();
            
            cmd.Connection = new SqlConnection("Data Source=frag-boardgame.kicks-ass.org\\SQLEXPRESS;Initial Catalog=frag;Persist Security Info=True;User ID=roger;Password=rabbit");
            cmd.CommandText = "dbo.frag_getUserHistory_byName";
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.Add(new SqlParameter("@i_user_name", username));
            
            cmd.Connection.Open();
            
            sqldr = cmd.ExecuteReader();
            
            while (sqldr.Read())
            {
                historyList.Add(sqldr.GetString(0));
            }
            cmd.Connection.Close();
            
            return historyList;            
        }

        public bool addBoard(string name, Board board)
        {
            SqlCommand cmd = new SqlCommand();
            bool success = false;
            try
            {

                DataContractSerializer dcs = new DataContractSerializer(typeof(Board));
                MemoryStream ms = new MemoryStream();
                dcs.WriteObject(ms, board);
                ms.Position = 0;
                StreamReader sr = new StreamReader(ms);
                string xml = sr.ReadToEnd();

                cmd.CommandText = "dbo.frag_Board_add";
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Connection = new SqlConnection("Data Source=frag-boardgame.kicks-ass.org\\SQLEXPRESS;Initial Catalog=frag;Persist Security Info=True;User ID=roger;Password=rabbit");
                cmd.Parameters.Add(new SqlParameter("@i_board_name", name));
                cmd.Parameters.Add(new SqlParameter("@i_board_xml", xml));

                cmd.Connection.Open();
                cmd.ExecuteNonQuery();
                cmd.Connection.Close();
                success = true;
            }
            catch (Exception e)
            {
                success = false;
            }
            return success;
            
        }

        public Board getBoard(string name)
        {
            Board board = null;

            SqlCommand cmd = new SqlCommand();
            
            bool success = false;
            try
            {
                
                cmd.CommandText = "dbo.frag_Board_get";
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Connection = new SqlConnection("Data Source=frag-boardgame.kicks-ass.org\\SQLEXPRESS;Initial Catalog=frag;Persist Security Info=True;User ID=roger;Password=rabbit");
                cmd.Parameters.Add(new SqlParameter("@i_board_name", name));

                cmd.Connection.Open();
                string xmlBoard = (string)cmd.ExecuteScalar();
                cmd.Connection.Close();
                
                MemoryStream ms = new MemoryStream();
                StreamWriter sw = new StreamWriter(ms);
                
                sw.Write(xmlBoard);
                sw.Flush();
                ms.Position = 0;
                StreamReader sr = new StreamReader(ms);
            
                DataContractSerializer dcs = new DataContractSerializer(typeof(Board));
                board = (Board)dcs.ReadObject(ms);
                
            }
             catch (Exception e)
            {
                board = null;
            }
            return board;
        }

        public List<String> getBoardsList()
        {
            SqlDataReader sqldr;
            List<String> boards = new List<string>();

            SqlCommand cmd = new SqlCommand();

            cmd.Connection = new SqlConnection("Data Source=frag-boardgame.kicks-ass.org\\SQLEXPRESS;Initial Catalog=frag;Persist Security Info=True;User ID=roger;Password=rabbit");
            cmd.CommandText = "dbo.frag_Board_getList";
            cmd.CommandType = CommandType.StoredProcedure;

            cmd.Connection.Open();
            sqldr = cmd.ExecuteReader();

            while (sqldr.Read())
            {
                boards.Add(sqldr.GetString(0));
            }
            cmd.Connection.Close();

            return boards;  
        
        }

        #endregion
    }
}
